import xlrd
import pymysql

# 打开Excel文件
workbook = xlrd.open_workbook('baidu.xls')
sheet = workbook.sheet_by_index(0)


# 单条查询
def dan(sql, parm=None):
    if parm is None:
        parm = []
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123',
        db='hongruan',
        charset='utf8'
    )
    # 通过连接创建控制台
    cursor = conn.cursor()

    # 执行
    cursor.execute(sql, parm)
    # 提交到数据库
    conn.commit()
    # 关闭资源
    cursor.close()
    conn.close()


# 多条查询
def excu(sql, parm=None):
    # 连接MySQL数据库
    if parm is None:
        parm = []
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123',
        db='hongruan',
        charset='utf8'
    )
    # 通过连接创建控制台
    cursor = conn.cursor()
    # 执行
    cursor.executemany(sql, parm)
    # 提交到数据库
    conn.commit()
    # 关闭资源
    cursor.close()
    conn.close()


sql = 'DELETE FROM user_info'
dan(sql)

sql1 = "INSERT INTO user_info VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
parm1 = []
a = 1000 * int(sheet.nrows//1000)
# 遍历Excel中的数据，写入数据库
for i in range(1, a + 1):
    data = sheet.row_values(i)
    parm1.append(data)
    if len(parm1) == 1000:
        excu(sql1, parm1)
        parm1.clear()

for j in range(a + 1, sheet.nrows):
    data = sheet.row_values(j)
    parm1.append(data)
    excu(sql1, parm1)
    parm1.clear()
print("数据已成功写入数据库！")
